/*
 * Copyright 1999-2022 Alibaba Group Holding Ltd.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.alibaba.nacos.plugin.datasource.impl.sqlserver;

import com.alibaba.nacos.common.utils.ArrayUtils;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.common.utils.NamespaceUtil;
import com.alibaba.nacos.common.utils.StringUtils;
import com.alibaba.nacos.plugin.datasource.constants.ContextConstant;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.mapper.ConfigInfoMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * The mysql implementation of ConfigInfoMapper.
 *
 * @author hyx
 **/

public class ConfigInfoMapperBySqlserver extends AbstractMapperBySqlserver implements ConfigInfoMapper {

    private static final String DATA_ID = "dataId";

    private static final String GROUP = "group";

    private static final String APP_NAME = "appName";

    private static final String CONTENT = "content";

    private static final String TENANT = "tenant";

    @Override
    public MapperResult findConfigInfoByAppFetchRows(MapperContext context) {
        final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
        final String tenantId = (String) context.getWhereParameter(FieldConstant.TENANT_ID);

        String sql = "SELECT id, data_id, group_id, tenant_id, app_name, content FROM config_info "
                + "WHERE tenant_id LIKE ? AND app_name = ? "
                + "ORDER BY id "
                + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        return new MapperResult(sql, CollectionUtils.list(tenantId, appName, context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult getTenantIdList(MapperContext context) {
        String sql = "SELECT tenant_id FROM config_info "
                + "WHERE tenant_id != ? "
                + "GROUP BY tenant_id "
                + "ORDER BY tenant_id "
                + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        return new MapperResult(sql, CollectionUtils.list(NamespaceUtil.getNamespaceDefaultId(), context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult getGroupIdList(MapperContext context) {
        String sql = "SELECT group_id FROM config_info "
                + "WHERE tenant_id = ? " // 使用参数化查询
                + "GROUP BY group_id "
                + "ORDER BY group_id " // 必须添加 ORDER BY 子句
                + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        return new MapperResult(sql, CollectionUtils.list(NamespaceUtil.getNamespaceDefaultId(), context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult findAllConfigKey(MapperContext context) {
        String sql = "SELECT t.data_id, t.group_id, t.app_name FROM ( "
                + " SELECT id FROM config_info WHERE tenant_id LIKE ? "
                + " ORDER BY id "
                + " OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ) g "
                + " JOIN config_info t ON g.id = t.id";
        return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.TENANT_ID),
                context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult findAllConfigInfoBaseFetchRows(MapperContext context) {

        String sql = "SELECT t.id, t.data_id, t.group_id, t.content, t.md5 "
                + "FROM ( "
                + "    SELECT id FROM config_info "
                + "    ORDER BY id "
                + "    OFFSET ? ROWS FETCH NEXT ? ROWS ONLY "
                + ") g "
                + "JOIN config_info t ON g.id = t.id";
        return new MapperResult(sql, CollectionUtils.list(context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult findAllConfigInfoFragment(MapperContext context) {
        String contextParameter = context.getContextParameter(ContextConstant.NEED_CONTENT);

        boolean needContent = contextParameter != null && Boolean.parseBoolean(contextParameter);
        String sql = "SELECT id, data_id, group_id, tenant_id, app_name, "
                + (needContent ? "content," : "")
                + " md5, gmt_modified, type, encrypted_data_key "
                + "FROM config_info "
                + "WHERE id > ? "
                + "ORDER BY id ASC "
                + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.ID), context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult findChangeConfigFetchRows(MapperContext context) {
        final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
        final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
        final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
        final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
        final String tenantTmp = StringUtils.isBlank(tenant) ? StringUtils.EMPTY : tenant;
        final Timestamp startTime = (Timestamp) context.getWhereParameter(FieldConstant.START_TIME);
        final Timestamp endTime = (Timestamp) context.getWhereParameter(FieldConstant.END_TIME);

        List<Object> paramList = new ArrayList<>();

        final String sqlFetchRows = "SELECT id, data_id, group_id, tenant_id, app_name, type, md5, gmt_modified FROM config_info WHERE ";
        StringBuilder where = new StringBuilder("1=1 ");

        if (!StringUtils.isBlank(dataId)) {
            where.append(" AND data_id LIKE ? ");
            paramList.add(dataId);
        }
        if (!StringUtils.isBlank(group)) {
            where.append(" AND group_id LIKE ? ");
            paramList.add(group);
        }

        if (!StringUtils.isBlank(tenantTmp)) {
            where.append(" AND tenant_id = ? ");
            paramList.add(tenantTmp);
        }

        if (!StringUtils.isBlank(appName)) {
            where.append(" AND app_name = ? ");
            paramList.add(appName);
        }
        if (startTime != null) {
            where.append(" AND gmt_modified >= ? ");
            paramList.add(startTime);
        }
        if (endTime != null) {
            where.append(" AND gmt_modified <= ? ");
            paramList.add(endTime);
        }

        int pageSize = context.getPageSize();
        String sql = sqlFetchRows + where.toString()
                + " AND id > ? "
                + " ORDER BY id ASC "
                + "OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY";
        paramList.add(context.getWhereParameter(FieldConstant.LAST_MAX_ID));
        paramList.add(pageSize);

        return new MapperResult(sql, paramList);
    }

    @Override
    public MapperResult listGroupKeyMd5ByPageFetchRows(MapperContext context) {
        String sql = "SELECT t.id, t.data_id, t.group_id, t.tenant_id, t.app_name, t.md5, t.type, t.gmt_modified, t.encrypted_data_key "
                + "FROM ( "
                + "    SELECT id FROM config_info "
                + "    ORDER BY id "
                + "    OFFSET ? ROWS FETCH NEXT ? ROWS ONLY "
                + ") g "
                + "JOIN config_info t ON g.id = t.id";
        return new MapperResult(sql, CollectionUtils.list(context.getStartRow(), context.getPageSize()));
    }

    @Override
    public MapperResult findConfigInfoBaseLikeFetchRows(MapperContext context) {
        final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
        final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
        final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);

        final String sqlFetchRows = "SELECT id, data_id, group_id, tenant_id, content FROM config_info WHERE ";
        StringBuilder where = new StringBuilder("1=1 AND tenant_id = ? ");

        List<Object> paramList = new ArrayList<>();
        paramList.add(NamespaceUtil.getNamespaceDefaultId());

        if (!StringUtils.isBlank(dataId)) {
            where.append(" AND data_id LIKE ? ");
            paramList.add(dataId);
        }
        if (!StringUtils.isBlank(group)) {
            where.append(" AND group_id LIKE ? ");
            paramList.add(group);
        }
        if (!StringUtils.isBlank(content)) {
            where.append(" AND content LIKE ? ");
            paramList.add(content);
        }

        // 添加 OFFSET-FETCH 语句
        int pageSize = context.getPageSize();
        int startRow = context.getStartRow();
        String sql = sqlFetchRows + where.toString()
                + " ORDER BY id "
                + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        paramList.add(startRow);
        paramList.add(pageSize);
        return new MapperResult(sql, paramList);
    }

    @Override
    public MapperResult findConfigInfo4PageFetchRows(MapperContext context) {
        final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
        final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
        final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
        final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
        final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);

        List<Object> paramList = new ArrayList<>();

        final String sql = "SELECT id, data_id, group_id, tenant_id, app_name, content, type, encrypted_data_key FROM config_info";
        StringBuilder where = new StringBuilder(" WHERE tenant_id = ? ");
        paramList.add(tenant);

        if (StringUtils.isNotBlank(dataId)) {
            where.append(" AND data_id = ? ");
            paramList.add(dataId);
        }
        if (StringUtils.isNotBlank(group)) {
            where.append(" AND group_id = ? ");
            paramList.add(group);
        }
        if (StringUtils.isNotBlank(appName)) {
            where.append(" AND app_name = ? ");
            paramList.add(appName);
        }
        if (!StringUtils.isBlank(content)) {
            where.append(" AND content LIKE ? ");
            paramList.add(content);
        }

        // 获取分页参数
        int startRow = context.getStartRow();
        int pageSize = context.getPageSize();

        // 修改 SQL 以使用 OFFSET-FETCH
        String finalSql = sql + where.toString()
                + " ORDER BY id "
                + "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

        paramList.add(startRow);
        paramList.add(pageSize);
        return new MapperResult(finalSql, paramList);
    }

    @Override
    public MapperResult findConfigInfoBaseByGroupFetchRows(MapperContext context) {
        String sql = "SELECT id, data_id, group_id, content FROM config_info WHERE group_id = ? AND tenant_id = ?";

        int startRow = context.getStartRow();
        int pageSize = context.getPageSize();

        final String finalSql = sql + " ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

        List<Object> paramList = new ArrayList<>();
        paramList.add(context.getWhereParameter(FieldConstant.GROUP_ID));
        paramList.add(context.getWhereParameter(FieldConstant.TENANT_ID));
        paramList.add(startRow);
        paramList.add(pageSize);

        return new MapperResult(finalSql, paramList);
    }

    @Override
    public MapperResult findConfigInfoLike4PageFetchRows(MapperContext context) {
        final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
        final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
        final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
        final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
        final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);
        final String[] types = (String[]) context.getWhereParameter(FieldConstant.TYPE);

        StringBuilder sqlBuilder = new StringBuilder(
                "SELECT id, data_id, group_id, tenant_id, app_name, content, encrypted_data_key, type FROM config_info WHERE tenant_id LIKE ?");

        List<Object> paramList = new ArrayList<>();
        paramList.add(tenant);

        if (StringUtils.isNotBlank(dataId)) {
            sqlBuilder.append(" AND data_id LIKE ?");
            paramList.add(dataId);
        }
        if (StringUtils.isNotBlank(group)) {
            sqlBuilder.append(" AND group_id LIKE ?");
            paramList.add(group);
        }
        if (StringUtils.isNotBlank(appName)) {
            sqlBuilder.append(" AND app_name = ?");
            paramList.add(appName);
        }
        if (StringUtils.isNotBlank(content)) {
            sqlBuilder.append(" AND content LIKE ?");
            paramList.add(content);
        }
        if (!ArrayUtils.isEmpty(types)) {
            sqlBuilder.append(" AND type IN (");
            sqlBuilder.append(String.join(",", Collections.nCopies(types.length, "?")));
            sqlBuilder.append(")");
            Collections.addAll(paramList, types);
        }

        // 添加排序和分页
        int startRow = context.getStartRow();
        int pageSize = context.getPageSize();

        sqlBuilder.append(" ORDER BY id ")
                .append("OFFSET ? ROWS FETCH NEXT ? ROWS ONLY");

        // 添加起始行和每页大小到参数列表
        paramList.add(startRow);
        paramList.add(pageSize);

        return new MapperResult(sqlBuilder.toString(), paramList);
    }

    @Override
    public MapperResult findAllConfigInfoFetchRows(MapperContext context) {
        String sql = "SELECT t.id, data_id, group_id, tenant_id, app_name, content, md5 "
                + "FROM ( "
                + "    SELECT id FROM config_info WHERE tenant_id LIKE ? "
                + "    ORDER BY id "
                + "    OFFSET ? ROWS FETCH NEXT ? ROWS ONLY "
                + ") g, config_info t WHERE g.id = t.id";
        return new MapperResult(sql,
                CollectionUtils.list(context.getWhereParameter(FieldConstant.TENANT_ID),
                        context.getStartRow(),
                        context.getPageSize()));
    }

    @Override
    public String getDataSource() {
        return DataSourceConstant.SQLSERVER;
    }

}
